﻿using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using VIT.DataHelper;

namespace VIT.Provider
{
    public class Folder : Folder_Base
    {
        #region Variable
        string user = HttpContext.Current.Session["userName"].ToString();
        #endregion

        #region Constructor
        public Folder(int Id = -1,
                        int ParentId = 0,
                        string LanguageId = "",
                        string Name = "",
                        string Description = "",
                        int orders = 50,
                        bool published = true)
        {
            this.Id = Id;
            this.ParentId = ParentId;
            this.LanguageId = LanguageId;
            this.Name = Name;
            this.Description = Description;
            this.Orders = orders;
            this.Published = published;
        }
        #endregion

        #region List
        public DataTable GetAll(string folderlist = "")
        {
            string strQuery = string.Empty;
            if (folderlist == "")
                strQuery = "select Id, Name, ParentId, Description, Orders, Published from Folder c left join Folder_Language l on Id=FolderId and LanguageID=@LanguageID where Deleted=0";
            else
                strQuery = "select Id, Name, ParentId, Description, Orders, Published from Folder c left join Folder_Language l on Id=FolderId and LanguageID=@LanguageID where Deleted=0 and Id in (" + folderlist + ")";
            SqlParameter LanguageId = new SqlParameter("@LanguageID", this.LanguageId);
            return ExecuteDataset(false, strQuery, LanguageId).Tables[0];
        }

        /* If Folder has children or files then it can't be deleted */
        public bool checkForDel(int id)
        {
            string strQuery = "select count(Id) from Folder where ParentId=@Id and Deleted=0";
            SqlParameter Id = new SqlParameter("@Id", id);
            int count = int.Parse(ExecuteScalar(false, strQuery, Id).ToString());
            if (count > 0)  return false; // has child folder
            
            strQuery = "select count(Files.Id) from Folder, Files where FolderId=@Id";
            count = int.Parse(ExecuteScalar(false, strQuery, Id).ToString());
            if (count > 0) return false;    // has file
            return true;
        }

        public bool Delete(int delete, int id)
        {
            string strQuery = "update Folder set Deleted=@Deleted ,ModifyDate=@Now, ModifyByUser=@User, DeleteByUser=@DeleteByUser where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter D = new SqlParameter("@Deleted", delete);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter DeleteByUser = new SqlParameter("@DeleteByUser", (delete==1)?user:"");
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            if (ExecuteNonQuery(false, strQuery, Id, D, User, DeleteByUser, Now)>0)
            {
                strQuery = "update Folder_Language set Published=@Published where FolderId=@ID";
                SqlParameter ID = new SqlParameter("@ID", id);
                SqlParameter P = new SqlParameter("@Published", (delete == 0) ? 1 : 0);
                return ExecuteNonQuery(false, strQuery, ID, P) > 0;
            }
            return false;
        }

        public bool Publish(int publish, int id)
        {
            string strQuery = "update Folder_Language set Published=@Published, ModifyDate=@Now, ModifyByUser=@User where FolderId=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter P = new SqlParameter("@Published", publish);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(false, strQuery, Id, P, User, Now) > 0;
        }

        public bool Order(string order, int id)
        {
            string strQuery = "update Folder_Language set Orders=Orders " + order + " 1, ModifyDate=@Now, ModifyByUser=@User where FolderId=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(false, strQuery, Id, User, Now) > 0;
        }
        #endregion

        #region Edit
        public void GetSingle()
        {
            if (Exist())
            {
                string strQuery = "select ParentId, Name, Description, Orders, Published from Folder c left join Folder_Language l on Id=FolderId and LanguageID=@LanguageId where Id=@Id";
                SqlParameter Id = new SqlParameter("@Id", this.Id);
                SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
                DataTable dt = ExecuteDataset(false, strQuery, Id, LanguageId).Tables[0];

                if (dt.Rows.Count > 0)
                {
                    if (!string.IsNullOrEmpty(dt.Rows[0]["Orders"].ToString()))
                        this.Orders = int.Parse(dt.Rows[0]["Orders"].ToString());
                    this.Name = dt.Rows[0]["Name"].ToString();
                    this.Description = dt.Rows[0]["Description"].ToString();
                    this.ParentId = Convert.ToInt32(dt.Rows[0]["ParentId"]);
                    if (!string.IsNullOrEmpty(dt.Rows[0]["Published"].ToString()))
                        this.Published = (bool)dt.Rows[0]["Published"];
                }
            }
            else this.Id = -1;
        }

        public bool Exist()
        {
            string strQuery = "select Id from Folder where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", this.Id);
            return ExecuteScalar(false, strQuery, Id) != null;
        }

        public bool Exist(int id, string languageid)
        {
            string strQuery = "select FolderId, LanguageID from Folder_Language where FolderId=@Id and LanguageID=@LanguageId";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter LanguageId = new SqlParameter("@LanguageId", languageid);
            return ExecuteScalar(false, strQuery, Id, LanguageId) != null;
        }

        public int Save()
        {
            if (Exist())
            {
                Delete(0, this.Id);
                UpdateObj(true);
                if (Exist(this.Id, this.LanguageId)) UpdateObjLang(true);
                else InsertObjLang(true);
            }
            else
            {
                this.Id = InsertObj(true);
                if (this.Id > 0) InsertObjLang(true);
            }
            return 0;
        }

        public int InsertObj(bool isTransaction)
        {
            string sqlCat = "insert into Folder(ParentId, Deleted, CreateDate, ModifyDate, CreateByUser, ModifyByUser) values (@ParentId, 0, @Now, @Now, @User, @User)";

            SqlParameter ParentId = new SqlParameter("@ParentId", this.ParentId);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(isTransaction, sqlCat, ParentId, User, Now);
        }

        public int InsertObjLang(bool isTransaction)
        {
            string sqlLang = "insert into Folder_Language (FolderId, LanguageId, Name, Description, Orders, Published, CreateDate, ModifyDate, CreateByUser, ModifyByUser) values (@FolderId, @LanguageId, @Name, @Description, @Orders, @Published, @Now, @Now, @User, @User)";
            SqlParameter FolderId = new SqlParameter("@FolderId", this.Id);
            SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
            SqlParameter Name = new SqlParameter("@Name", this.Name);
            SqlParameter Description = new SqlParameter("@Description", this.Description);
            SqlParameter Orders = new SqlParameter("@Orders", this.Orders);
            SqlParameter Published = new SqlParameter("@Published", this.Published);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);
            return ExecuteNonQuery(isTransaction, sqlLang, FolderId, LanguageId, Name, Description, Orders, Published, User, Now);
        }

        public int UpdateObj(bool isTransaction)
        {
            string sqlCat = "update Folder set ParentId=@ParentId, ModifyDate=@Now, ModifyByUser=@User where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", this.Id);
            SqlParameter ParentId = new SqlParameter("@ParentId", this.ParentId);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(isTransaction, sqlCat, Id, ParentId, User, Now);
        }

        public int UpdateObjLang(bool isTransaction)
        {
            string sqlLang = "update Folder_Language set Name=@Name, Description=@Description, Orders=@OrderVal, Published=@Published, ModifyDate=@Now, ModifyByUser=@User where FolderId=@FolderId and LanguageId=@LanguageId";
            SqlParameter FolderId = new SqlParameter("@FolderId", this.Id);
            SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
            SqlParameter Name = new SqlParameter("@Name", this.Name);
            SqlParameter Description = new SqlParameter("@Description", this.Description);
            SqlParameter OrderVal = new SqlParameter("@OrderVal", this.Orders);
            SqlParameter Published = new SqlParameter("@Published", this.Published);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(isTransaction, sqlLang, FolderId, LanguageId, Name, Description, OrderVal, Published, User, Now);
        }
        #endregion

        #region Trash
        public string getParentId(int delete, string id)
        {
            string strQuery = "select parent.Id from Folder child, Folder parent where child.Id=@Id and parent.Deleted=@Delete and parent.Id=child.ParentId";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter d = new SqlParameter("@Delete", delete);
            object obj = ExecuteScalar(false, strQuery, Id, d);
            return (obj != null) ? obj.ToString() : "";
        }

        public DataTable GetRecycleBin(string SortColumn)
        {
            string strQuery;
            if (SortColumn == "")
                strQuery = "select Id, ParentId, CreateDate, ModifyDate, CreateByUser, ModifyByUser, DeleteByUser from Folder where Deleted=1";
            else
                strQuery = "select Id, ParentId, CreateDate, ModifyDate, CreateByUser, ModifyByUser, DeleteByUser from Folder where Deleted=1 order by " + SortColumn;
            return ExecuteDataset(false, strQuery).Tables[0];
        }

        public bool Cleared(string id)
        {
            string strQuery = "delete from Folder where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            return ExecuteNonQuery(false, strQuery, Id)>0;
        }
        #endregion
    }
}
